/**
 * Description:
 * Class Name:ResultSetTest
 * Date:2022/4/9 20:40
 *
 * @author LTisme
 * @version JAVA17
 */
package 疯狂Java讲义.第13章_MySQL数据库和JDBC编程._13_5_管理结果集._13_5_1_可滚动_可更新的结果集;

import java.io.FileInputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.Properties;

public class ResultSetTest {
    private String driver;
    private String url;
    private String user;
    private String pwd;

    public void initParam(String paramFile) throws Exception{
        Properties props = new Properties();
        props.load(new FileInputStream(paramFile));
        driver = props.getProperty("driver");
        url = props.getProperty("url");
        user = props.getProperty("user");
        pwd = props.getProperty("pwd");
    }

    public void doQuery(String sql) throws Exception{
        try(
                Connection connection = DriverManager.getConnection(url, user, pwd);
                PreparedStatement pstmt = connection.prepareStatement(sql, ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE);
                ResultSet resultSet = pstmt.executeQuery(sql)       // 这里直接执行查询，不使用占位符
                ){
            resultSet.last();
            int rowCount = resultSet.getRow();      // 取回当前行的行号。第一行行号为1，第二行行号为2，以此类推

            for (int i = rowCount; i > 0; i--) {
                resultSet.absolute(i);              // 将记录指针移动到指定的行号所在的行
                // 打印未修改的指定行的前3列
                System.out.println(resultSet.getString(1) + "\t"
                + resultSet.getString(2) + "\t"
                + resultSet.getString(3));
                // 修改记录指针所指记录的第2列的值
                resultSet.updateString(2, "学生名_" + i + "_可滚动更新的ResultSet");
                // 提交修改
                resultSet.updateRow();
            }
        }
    }

    public static void main(String[] args) throws Exception{
        var rst = new ResultSetTest();
        rst.initParam("Chapter_13_mysql.ini");
        Class.forName(rst.driver);

        String sql = "select * from student_table";
        rst.doQuery(sql);
    }
}
